package com.aboo.word2excel;

import com.aboo.word2excel.attr.CellAttribute;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableRow;
import org.junit.jupiter.api.Test;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.function.Function;
import java.util.function.Predicate;
import java.util.stream.Collectors;

/**
 * @author louan
 * @date 2025-01-07
 * @description
 */
public class Word2ExcelTest03 {

    @Test
    public void test01() throws IOException {
        String wordFilePath = "D:/workspace_data/JavaStudy20250106/24ZAS02P03D33-35532.docx";
        String excelFilePath = "D:/workspace_data/JavaStudy20250106/24ZAS02P03D33-35532.xlsx";

        // 指定符合预期数据的条件（红色文本的单元格）
        Predicate<XWPFTableCell> predicate = cell -> {
            for (XWPFParagraph paragraph : cell.getParagraphs()) {
                for (XWPFRun run : paragraph.getRuns()) {
                    if (run.getColor() != null && run.getColor().equals("FF0000")) {
                        return true;
                    }
                }
            }
            return false;
        };

        // 获取单元格属性
        List<CellAttribute> cellAttributes = getCellAttributes(wordFilePath, predicate);
        // 将Word中的表格迁移到Excel
        transferWordTable2Excel(wordFilePath, excelFilePath, predicate);
        // 单元格合并
        mergeExcelCells(excelFilePath, cellAttributes);
        // 设置单元格宽度和高度
        setCellWidthHeight(excelFilePath, cellAttributes);
        // 设置Excel区域边框
        setExcelRegionBorder(excelFilePath, cellAttributes);
    }

    /**
     * @param excelFilePath
     * @param cellAttributes
     * @throws IOException
     * @description 设置单元格宽度和高度
     */
    private static void setCellWidthHeight(String excelFilePath, List<CellAttribute> cellAttributes) throws IOException {
        Map<String, CellAttribute> cellAttributeMap = cellAttributes.stream().collect(Collectors.toMap((cell) -> cell.getRowNo() + "," + cell.getColNo(), Function.identity()));

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet

            // 设置每个单元格的宽度和高度
            for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row != null) {
                    for (int colIndex = 0; colIndex < row.getLastCellNum(); colIndex++) {
                        String key = rowIndex + "," + colIndex;
                        CellAttribute cellAttribute = cellAttributeMap.get(key);
                        if (cellAttribute == null) {
                            sheet.setColumnWidth(colIndex, 0);
                            continue;
                        }

                        //row.setHeightInPoints(cellAttribute.getHeight());
                        sheet.setColumnWidth(colIndex, cellAttribute.getWidth() * 5);
//                        row.setHeightInPoints(30); // 设置行高为30点
//                        sheet.setColumnWidth(colIndex, 20 * 256); // 设置列宽为20字符宽度
                    }
                }
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param wordFilePath
     * @param predicate
     * @return
     * @throws IOException
     * @description 获取单元格属性
     */
    private static List<CellAttribute> getCellAttributes(String wordFilePath, Predicate<XWPFTableCell> predicate) throws IOException {
        List<CellAttribute> cellAttributes = new ArrayList<>(32);

        try (FileInputStream fis = new FileInputStream(wordFilePath);
             XWPFDocument document = new XWPFDocument(fis)) {

            for (XWPFTable table : document.getTables()) {
                for (int rowIndex = 0; rowIndex < table.getRows().size(); rowIndex++) {
                    XWPFTableRow row = table.getRow(rowIndex);
                    int gap = 0;
                    for (int colIndex = 0; colIndex < row.getTableCells().size(); colIndex++) {
                        XWPFTableCell cell = row.getCell(colIndex);
                        int rowSpan = getRowSpan(cell);
                        int colSpan = getColSpan(cell);
                        // 宽度的单位是dxa（twentieths of a point）。1 dxa = 1 twip
                        int cellWidth = cell.getWidth();
                        // 高度的单位是twips（twentieths of a point）。1 point = 20 twips
                        int cellHeight = row.getHeight();
                        String cellText = cell.getText();

                        if (predicate != null && !predicate.test(cell)) {
                            gap += (colSpan - 1);
                            continue;
                        }

                        CellAttribute cellAttribute = CellAttribute.of(rowIndex, colIndex + gap, rowSpan, colSpan, cellWidth, cellHeight, cell.getText());
                        cellAttributes.add(cellAttribute);

                        gap += (colSpan - 1);
                    }
                }
            }
        }

        correctCellAttributes(cellAttributes);
        return cellAttributes;
    }

    /**
     * @param cellAttributes
     * @description 修正单元格属性
     */
    private static void correctCellAttributes(List<CellAttribute> cellAttributes) {
        int minRowNo = cellAttributes.stream().mapToInt(CellAttribute::getRowNo).min().orElse(0);
        int minColNo = cellAttributes.stream().mapToInt(CellAttribute::getColNo).min().orElse(0);
        if (minRowNo != 0 || minColNo != 0) {
            for (CellAttribute cell : cellAttributes) {
                cell.setRowNo(cell.getRowNo() - minRowNo);
                cell.setColNo(cell.getColNo() - minColNo);
            }
        }

        // 按行号和列号排序
        cellAttributes.sort(Comparator.comparing(CellAttribute::getRowNo).thenComparing(CellAttribute::getColNo));
    }

    /**
     * @param wordFilePath
     * @param excelFilePath
     * @throws IOException
     * @description 将Word中的表格迁移到Excel
     */
    private void transferWordTable2Excel(String wordFilePath, String excelFilePath, Predicate<XWPFTableCell> predicate) throws IOException {
        try (FileInputStream fis = new FileInputStream(wordFilePath);
             XWPFDocument document = new XWPFDocument(fis);
             Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Red Text Tables");

            int rowIndex = 0;
            for (XWPFTable table : document.getTables()) {
                for (XWPFTableRow row : table.getRows()) {
                    boolean hasRedText = false;
                    for (XWPFTableCell cell : row.getTableCells()) {
                        hasRedText = predicate.test(cell);
                        if (hasRedText) {
                            break;
                        }
                    }
                    if (hasRedText) {
                        Row excelRow = sheet.createRow(rowIndex++);
                        int cellIndex = 0;
                        for (XWPFTableCell cell : row.getTableCells()) {
                            Cell excelCell = excelRow.createCell(cellIndex++);
                            String cellText = cell.getText();
                            excelCell.setCellValue(cell.getText());

                            // Handle merged cells
                            if (cell.getCTTc().isSetTcPr() && cell.getCTTc().getTcPr().isSetGridSpan()) {
                                int colspan = cell.getCTTc().getTcPr().getGridSpan().getVal().intValue();
                                for (int i = 1; i < colspan; i++) {
                                    excelCell = excelRow.createCell(cellIndex++);
                                    excelCell.setCellValue("");
                                }
                            }
                        }
                    }
                }
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param excelFilePath
     * @param cellAttributes
     * @throws IOException
     * @description 单元格合并
     */
    private static void mergeExcelCells(String excelFilePath, List<CellAttribute> cellAttributes) throws IOException {
        List<CellAttribute> mergedCells = cellAttributes.stream().filter(cell -> cell.getRowSpan() > 1 || cell.getColSpan() > 1).toList();

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {
            Sheet sheet = workbook.getSheetAt(0);
            for (CellAttribute cell : mergedCells) {
                sheet.addMergedRegion(new CellRangeAddress(cell.getRowNo(), cell.getRowNo() + cell.getRowSpan() - 1, cell.getColNo(), cell.getColNo() + cell.getColSpan() - 1));
            }

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * @param excelFilePath
     * @param cellAttributes
     * @throws IOException
     * @description 设置Excel区域边框
     */
    private static void setExcelRegionBorder(String excelFilePath, List<CellAttribute> cellAttributes) throws IOException {
        int firstRow = cellAttributes.stream().mapToInt(CellAttribute::getRowNo).min().orElse(0);
        int lastRow = cellAttributes.stream().mapToInt(cell -> cell.getRowNo() + cell.getRowSpan() - 1).max().orElse(0);
        int firstCol = cellAttributes.stream().mapToInt(CellAttribute::getColNo).min().orElse(0);
        int lastCol = cellAttributes.stream().mapToInt(cell -> cell.getColNo() + cell.getColSpan() - 1).max().orElse(0);

        try (FileInputStream fis = new FileInputStream(excelFilePath);
             Workbook workbook = new XSSFWorkbook(fis)) {

            // 获取第一个Sheet
            Sheet sheet = workbook.getSheetAt(0);
            // 指定区域
            CellRangeAddress region = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);

            setRegionBorder(sheet, region, BorderStyle.THIN, IndexedColors.BLACK.getIndex());

            try (FileOutputStream fos = new FileOutputStream(excelFilePath)) {
                workbook.write(fos);
            }
        }
    }

    private static void setRegionBorder(Sheet sheet, CellRangeAddress region, BorderStyle borderStyle, short color) {
        for (int rowIndex = region.getFirstRow(); rowIndex <= region.getLastRow(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                row = sheet.createRow(rowIndex);
            }
            for (int colIndex = region.getFirstColumn(); colIndex <= region.getLastColumn(); colIndex++) {
                Cell cell = row.getCell(colIndex);
                if (cell == null) {
                    cell = row.createCell(colIndex);
                }
                CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
                cellStyle.cloneStyleFrom(cell.getCellStyle());
                cellStyle.setBorderTop(borderStyle);
                cellStyle.setBorderBottom(borderStyle);
                cellStyle.setBorderLeft(borderStyle);
                cellStyle.setBorderRight(borderStyle);
                cellStyle.setTopBorderColor(color);
                cellStyle.setBottomBorderColor(color);
                cellStyle.setLeftBorderColor(color);
                cellStyle.setRightBorderColor(color);
                cell.setCellStyle(cellStyle);
            }
        }
    }

    private static int getRowSpan(XWPFTableCell cell) {
        if (cell.getCTTc().isSetTcPr() && cell.getCTTc().getTcPr().isSetVMerge()) {
            return cell.getCTTc().getTcPr().getVMerge().getVal().intValue();
        }
        return 1; // Default row span is 1
    }

    private static int getColSpan(XWPFTableCell cell) {
        if (cell.getCTTc().isSetTcPr() && cell.getCTTc().getTcPr().isSetGridSpan()) {
            return cell.getCTTc().getTcPr().getGridSpan().getVal().intValue();
        }
        return 1; // Default column span is 1
    }
}
